package com.dc.management.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.dc.connectionfactory.ConnectionFactory;
import com.dc.management.pojo.News;

public class NewsDAO {
	
	public boolean insert(News news){
		
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			con = ConnectionFactory.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(" INSERT INTO news( TITILE, CONTENT, PUBLISHER_ID, PUBLISH_TIME ) " );
			sql.append(" VALUES(?,?,?,?) ");
			
			pstm = con.prepareStatement(sql.toString());
			pstm.setString(1, news.getTitle() );
			pstm.setString(2, news.getContent() );
			pstm.setString(3, news.getPublisher_id() );
			pstm.setString(4, news.getPublish_time() );
			
			
			return pstm.execute();
			
		}catch(Exception ex){
			System.out.println(ex.getMessage());
		}
		finally{
			ConnectionFactory.close(con, pstm, rs);
		}
		return false;
		
	}
	
	public List<News> listNews(){
		List<News> newses = new ArrayList<News>();
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			con = ConnectionFactory.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT id,titile,content,publisher_id,publish_time FROM news ");
			pstm = con.prepareStatement(sql.toString());
			rs = pstm.executeQuery();
			while(rs.next()){
				News news = new News();
				news.setId(rs.getInt("id"));
				news.setTitle(rs.getString("titile"));
				news.setContent(rs.getString("content"));
				news.setPublisher_id(rs.getString("publisher_id"));
				news.setPublish_time(rs.getString("publish_time"));
				
				newses.add(news);
			}
			return newses;
		}catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		finally{
			ConnectionFactory.close(con, pstm, rs);
		}
		
	}

	public List<News> listNewsByPageAndTime(int pageIndex, int pageSizeInt, String publish_time) {
		
		List<News> newses = new ArrayList<News>();
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			con = ConnectionFactory.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT id,titile,content,icon,publisher_id,publish_time FROM news where publish_time > ? order by publish_time limit ?,? ");
			pstm = con.prepareStatement(sql.toString());
			pstm.setString(1, publish_time);
			pstm.setInt(2, pageIndex-1);
			pstm.setInt(3, pageSizeInt);
			
			rs = pstm.executeQuery();
			while(rs.next()){
				News news = new News();
				news.setId(rs.getInt("id"));
				news.setTitle(rs.getString("titile"));
				news.setIcon(rs.getString("icon"));
				news.setContent(rs.getString("content"));
				news.setPublisher_id(rs.getString("publisher_id"));
				news.setPublish_time(rs.getString("publish_time"));
				
				newses.add(news);
			}
			return newses;
		}catch(Exception ex){
			ex.printStackTrace();
			return null;
		}
		finally{
			ConnectionFactory.close(con, pstm, rs);
		}
	}

}
